package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import server.database.ConnectionDatabase;

import mockup.AppointmentMockup;
import mockup.Mockup;
import mockup.ReservationMockup;

public class AppoitmentDB implements DatabaseAction {

	private String query;
	private Statement st;
	
	public AppoitmentDB() {
		try {
			st = ConnectionDatabase.getConnection().createStatement();		
		} catch (Exception e) {
			System.err.println("AppoitmentDB::Constructor:Exception: " + e.getMessage());
		}
	}

	@Override
	public int insert(Mockup m) {
		int dbID = -1;
		AppointmentMockup app = (AppointmentMockup) m;
		if(0 > app.getId() ) {
			query = "INSERT into " + ConnectionDatabase.getNameDB()+".appointment( " +
			"title, description, location, reservation_id, owner_id) " +
			"values('" + app.getTitle() + "','" +
			app.getDescription() + "','" +
			app.getLocation() + "'," + 
			app.getReservation().getId() + ", " +
			app.getOwnerID() + ")";
			
			dbID = ConnectionDatabase.insert(query);

		} else {
			update(m);
		}
		return dbID;
	}


	@Override
	public void delete(Mockup m) {
		AppointmentMockup app = (AppointmentMockup) m;
		if(app.getId() > -1) {
			query = "DELETE FROM " + ConnectionDatabase.getNameDB() + ".appointment " +
			"where appointment_id = " + app.getId();

			ConnectionDatabase.update(query, st);
		}
	}

	@Override
	public AppointmentMockup get(int id) {
		if(id > -1) {
			String query = "select * from " + ConnectionDatabase.getNameDB() + ".appointment " + 
			"where appoitment_id = " + id;
			ResultSet rs = ConnectionDatabase.get(query, st);
			try {
				while (rs.next()) {
					int appointments_id = rs.getInt("appointment_id");
					String title = rs.getString("title");
					String description = rs.getString("description");
					String location = rs.getString("location");	
					int res_id = rs.getInt("reservation_id");
					int owner_id = rs.getInt("owner_id");
					
					ReservationMockup res = (ReservationMockup) DataHandler.reservation.get(res_id);
					AppointmentMockup appointment = new AppointmentMockup(appointments_id, title, description, location, owner_id, res);
					return appointment;
				}
			} catch (SQLException e ) {
				System.err.println("AppoitmentDB::get:SQLException: " + e.getMessage());
			}
		} else {
			System.err.println("0 > id , søker ikke i database");
		}
		return null;
	}

	@Override
	public List<AppointmentMockup> getAll() {
		List<AppointmentMockup> apps = new ArrayList<AppointmentMockup>();
		query = "select appointment_id, title, description, " +
				"location, reservation_id, owner_id  from " 
				+ ConnectionDatabase.getNameDB() + ".appointment";
		ResultSet rs = ConnectionDatabase.get(query, st);
		try {
			while (rs.next()) {
				int appointment_id = rs.getInt("appointment_id");
				String title = rs.getString("title");
				String description = rs.getString("description");
				String location = rs.getString("location");	
				int res_id = rs.getInt("reservation_id");
				int owner_id = rs.getInt("owner_id");
				
				ReservationMockup res = (ReservationMockup) DataHandler.reservation.get(res_id);
				AppointmentMockup current = new AppointmentMockup(appointment_id, title, description, location, owner_id, res);
				apps.add(current);
			}
		} catch (SQLException e ) {
			System.err.println("AppoitmentDB::getAll:SQLException: " + e.getMessage());
		}
		return apps;
	}

	@Override
	public void update(Mockup m) {
		AppointmentMockup app = (AppointmentMockup) m;
		if(app.getId() > -1) {
			query = "UPDATE " + ConnectionDatabase.getNameDB() + ".appointment SET " +
			"title = '" + app.getTitle() + "'," +
			"description = '" + app.getDescription() + "'," + 
			"location = '" + app.getLocation() + "'," + 
			"reservation_id = " + app.getReservation().getId() + "," + 
			"owner_id = " + app.getOwnerID() + " " +
			"where appointment_id = " + app.getId();

			ConnectionDatabase.update(query, st);

		} else {
			System.err.println("Avtale har ikke ID; bruk insert i stedet");
		}
	}

	@Override
	public void emptyTable() {
		query = "TRUNCATE TABLE " + ConnectionDatabase.getNameDB() + ".appointment";
		ConnectionDatabase.update(query, st);
	}
}
